#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2022/11/24 11:48
# @Author : 闫峰
# @File    : sql_server_utils.py
# encoding=utf8
import json

# import ceshi
from datetime import datetime
from typing import Union

import pyodbc

from common.setting import ensure_path_sep
from utils.readFileUtils.yamlControl import GetYamlData


class MSSQL:

    def __init__(self, yaml_path, database_name):
        """
        yaml_path 数据库配置信息yaml 文件地址
        database_name  yaml文件中的数据库信息 key
        """
        mysql = GetYamlData(yaml_path).get_yaml_data()[database_name]
        # print(mysql)
        self.host = mysql['host']
        self.user = mysql['user']
        self.pwd = mysql['password']
        self.db = mysql['db_name']
        self.charset = mysql['charset']

    def __GetConnect(self):
        """
        得到连接信息
        返回: conn.cursor()
        """
        if not self.db:
            raise (NameError, "没有设置数据库信息")
        # self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db, charset=self.charset, as_dict=True)
        self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db)
        cur = self.conn.cursor()
        if not cur:
            raise (NameError, "连接数据库失败")
        else:
            return cur

    def __GetConnect_dict(self):
        """
        得到连接信息
        返回: conn.cursor()
        """
        if not self.db:
            raise (NameError, "没有设置数据库信息")
        self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db,
                                    charset=self.charset, as_dict=True)
        # self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db, charset=self.charset)
        cur = self.conn.cursor()
        if not cur:
            raise (NameError, "连接数据库失败")
        else:
            return cur

    def ExecQuery(self, sql, value):
        """
        执行查询语句
        返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段

        """
        cur = self.__GetConnect()
        cur.execute(sql, value)
        resList = cur.fetchall()

        # 查询完毕后必须关闭连接
        self.conn.close()
        return resList

    def ExecQuery_dict(self, sql, value):
        """
        执行查询语句
        返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段

        """
        cur = self.__GetConnect_dict()
        cur.execute(sql, value)
        resList = cur.fetchall()

        # 查询完毕后必须关闭连接
        for i in resList:
            self.verify(i)

        self.conn.close()
        return resList

    def ExecNonQuery(self, sql):
        """
        执行非查询语句

        调用示例：
            cur = self.__GetConnect()
            cur.execute(sql)
            self.conn.commit()
            self.conn.close()
        """
        cur = self.__GetConnect()
        cur.execute(sql)
        self.conn.commit()
        self.conn.close()

    def verify(self, result: dict) -> Union[dict, None]:
        """验证结果能否被json.dumps序列化"""
        # 尝试变成字符串，解决datetime 无法被json 序列化问题
        try:
            json.dumps(result)
        except TypeError:  # TypeError: Object of type datetime is not JSON serializable
            for k, v in result.items():
                if isinstance(v, datetime):
                    result[k] = str(v)[0:10]
        # return result

    def close(self):
        self.close()


import pymssql
from contextlib import contextmanager

class SQLServerDB:
    # def __init__(self, server, database, user, password):
    #     """
    #     初始化数据库连接参数
    #     """
    #     self.server = server
    #     self.database = database
    #     self.user = user
    #     self.password = password
    #     self.conn = None

    def __init__(self, yaml_path, database_name):

        """
        yaml_path 数据库配置信息yaml 文件地址
        database_name  yaml文件中的数据库信息 key
        """
        mysql = GetYamlData(yaml_path).get_yaml_data()[database_name]
        # print(mysql)
        self.server = mysql['host']
        self.user = mysql['user']
        self.password = mysql['password']
        self.database = mysql['db_name']
        self.database = mysql['db_name']
        self.database = mysql['db_name']
        conn_str = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={self.server},{1433};DATABASE={self.database};UID={self.user};PWD={self.password}'
        self.conn = pyodbc.connect(conn_str)


    @contextmanager
    def get_cursor(self):
        """
        获取数据库游标，使用 context manager 来确保连接的开启和关闭
        """
        if self.conn is None or self.conn.closed:
            self.conn = pymssql.connect(server=self.server,
                                        user=self.user,
                                        password=self.password,
                                        database=self.database)
        try:
            cur = self.conn.cursor()
            yield cur
        finally:
            cur.close()

    def execute_query(self, query, params=None):
        """
        执行查询并返回结果
        """
        with self.get_cursor() as cur:
            cur.execute(query, params or ())
            return cur.fetchall()

    def execute_non_query(self, query, params=None):
        """
        执行非查询操作（如 INSERT、UPDATE、DELETE），返回受影响的行数
        """
        with self.get_cursor() as cur:
            cur.execute(query, params or ())
            self.conn.commit()  # 提交事务
            return cur.rowcount

    def close(self):
        """
        关闭数据库连接
        """
        if self.conn:
            self.conn.close()
            self.conn = None


